package com.example.zsydb.repository;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Repository
public class PumpRepository {

    @Autowired
    @Qualifier("primaryJdbcTemplate") // 指定使用主数据源的JdbcTemplate
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private ResultRepository resultRepository;

    public List<Map<String, Object>> findAll(int page, int size, String jh) {
        // 动态构建基础SQL
        StringBuilder baseSql = new StringBuilder("SELECT * FROM PUMPJACK_VIEW");
        List<Object> params = new ArrayList<>();

        // 添加JH条件
        if (jh != null && !jh.isEmpty()) {
            baseSql.append(" WHERE JH = ?");
            params.add(jh);
        }

        // 分页SQL
        String sql = "SELECT * FROM (" +
                "   SELECT t.*, ROWNUM rn FROM (" +
                "       " + baseSql.toString() +
                "   ) t WHERE ROWNUM <= ?" +
                ") WHERE rn > ?";

        // 计算分页参数
        int start = (page - 1) * size;
        int end = page * size;
        params.add(end);
        params.add(start);

        // 执行分页查询
        List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, params.toArray());
        // 新增：统一时间字段格式
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (Map<String, Object> row : data) {
            Object timeValue = row.get("DYNA_CREATE_TIME");
            try {
                if (timeValue instanceof Timestamp) {
                    row.put("DYNA_CREATE_TIME", sdf.format((Timestamp) timeValue));
                } else if (timeValue instanceof Date) {
                    row.put("DYNA_CREATE_TIME", sdf.format((Date) timeValue));
                } else if (timeValue != null) {
                    String timeStr = timeValue.toString();
                    row.put("DYNA_CREATE_TIME", timeStr.endsWith(".0") ?
                            timeStr.substring(0, timeStr.length()-2) : timeStr);
                }
            } catch (Exception e) {
                // 异常处理
                row.put("DYNA_CREATE_TIME", "");
            }
            Object timeValue1 = row.get("CHECK_DATE");
            try {
                if (timeValue1 instanceof Timestamp) {
                    row.put("CHECK_DATE", sdf.format((Timestamp) timeValue1));
                } else if (timeValue1 instanceof Date) {
                    row.put("CHECK_DATE", sdf.format((Date) timeValue1));
                } else if (timeValue1 != null) {
                    String timeStr = timeValue1.toString();
                    row.put("CHECK_DATE", timeStr.endsWith(".0") ?
                            timeStr.substring(0, timeStr.length()-2) : timeStr);
                }
            } catch (Exception e) {
                // 异常处理
                row.put("CHECK_DATE", "");
            }
        }


        // 查询dba04数据并合并（仅当主数据中存在空字段时）
        if (!data.isEmpty()) {
            // 收集需要补全数据的JH（至少有一个字段为空）
            Set<String> jhSet = data.stream()
                    .filter(this::hasNullFields)
                    .map(row -> (String) row.get("JH"))
                    .collect(Collectors.toSet());

            if (!jhSet.isEmpty()) {
                List<Map<String, Object>> dbaData = findDba04Data(jhSet);

                Map<String, Map<String, Object>> dbaMap = dbaData.stream()
                        .collect(Collectors.toMap(
                                row -> (String) row.get("JH"),
                                row -> row
                        ));

                // 仅处理需要补全的JH
                data.forEach(row -> {
                    String currentJh = (String) row.get("JH");
                    if (jhSet.contains(currentJh)) {
                        Map<String, Object> dbaRow = dbaMap.get(currentJh);
                        if (dbaRow != null) {
                            replaceNulls(row, "YY", dbaRow);
                            replaceNulls(row, "TY", dbaRow);
                            replaceNulls(row, "HS", dbaRow);
                            replaceNulls(row, "BJ", dbaRow);
                            replaceNulls(row, "BS", dbaRow);
                        }
                    }
                });
            }
        }
        return data;
    }


    // 检查指定字段是否存在空值
    private boolean hasNullFields(Map<String, Object> row) {
        return row.get("YY") == null ||
                row.get("TY") == null ||
                row.get("HS") == null ||
                row.get("BJ") == null ||
                row.get("BS") == null;
    }

    private List<Map<String, Object>> findDba04Data(Set<String> jhSet) {
        if (jhSet.isEmpty()) {
            return Collections.emptyList();
        }

        String inClause = String.join(",", Collections.nCopies(jhSet.size(), "?"));
        String sql = "SELECT JH, YY, TY, HS, BJ, BS FROM dba04 " +
                "WHERE JH IN (" + inClause + ") " +
                "AND ny = TO_CHAR(ADD_MONTHS(SYSDATE,-2),'yyyymm')";

        return jdbcTemplate.queryForList(sql, jhSet.toArray());
    }

    private void replaceNulls(Map<String, Object> target, String field, Map<String, Object> source) {
        if (target.get(field) == null && source.containsKey(field)) {
            target.put(field, source.get(field));
        }
    }
}